package pc.system;

import java.util.*;
import java.sql.*;
import pc.util.*;
import pc.sql.value.*;

public class Table extends Actions
{
  private Hashtable params  = new Hashtable();
  private Hashtable fields = new Hashtable();
  private String table_name = null;

  public Table(Actions owner,String table_name)
  {
    super(owner);
    this.table_name = table_name;
    Record rc = new Record(getSQLCommand().executeQuery("DESCRIBE " + table_name));
    rc.beforeFirst();
    while (rc.next()) {
      fields.put(rc.getString("field").toLowerCase(),rc.getString("type").toLowerCase());
    }
    rc.close();
  }

  public String getName()
  {
    return this.table_name;
  }

  public ResultSet getResult(long id)
  {
    if (table_name == null || table_name.length() == 0) return null;
    return getSQLCommand().executeQuery("SELECT * FROM " + table_name + " WHERE id = " + id);
  }

  public Record getRecord(long id)
  {
    return new Record(getResult(id));
  }

  public ResultSet getList(String field_name,int active)
  {
    String sql =
        "SELECT id," + field_name + " AS name " +
        "FROM " + table_name + " " +
        "WHERE (active = ? OR ?) " +
        "ORDER BY name,id";

    Vector vp = new Vector();

    vp.addElement(new IntValue(active));
    vp.addElement(new BooleanValue(active==2));

    return this.getSQLCommand().executeQuery(sql,vp);
  }

  public ResultSet getList(String field_name)
  {
    return getList(field_name,2);
  }

  public ResultSet getList(int active)
  {
    return getList("name",active);
  }

  public ResultSet getList()
  {
    return getList(2);
  }

  public ResultSet getResultSet(int active)
  {
    if (table_name == null || table_name.length() == 0) return null;
    String sql =
        "SELECT * FROM " + table_name + " " +
        "WHERE (active = ? OR ?)";
    Vector vp = new Vector();
    vp.addElement(new IntValue(active));
    vp.addElement(new BooleanValue(active==2));
    return this.getSQLCommand().executeQuery(sql,vp);
  }

  public ResultSet getResultSet()
  {
    return getResultSet(2);
  }

  public long getCount()
  {
    String sql = "SELECT COUNT(*) AS count FROM " + this.table_name;
    ResultSet rs = getSQLCommand().executeQuery(sql);
    Record rc = new Record(rs);
    long value = rc.getLongValue("count");
    rc.close();
    return value;
  }

  public long getLastID()
  {
    if (table_name == null || table_name.length() == 0) return 0;
    String sql = "SELECT MAX(id) as last_id FROM " + table_name;
    Record rc = new Record(getSQLCommand().executeQuery(sql));
    long last_id = rc.getLongValue("last_id");
    rc.close();
    return last_id;
  }
/*
  public ResultSet getFields()
  {
    return getSQLCommand().executeQuery("DESCRIBE " + table_name);
  }
*/

  public void clearParams()
  {
    params.clear();
  }

  public void removeParam(String field_name)
  {
    params.remove(field_name);
  }

  public void addParam(String field_name,String value, String type)
  {
    if (value == null || type == null) return;
    value = value.trim();
    if (Utils.isDateStr(value)) {
      java.sql.Date date = Utils.strToDate(value);
      value = Utils.toString(date);
      params.put(field_name,value);
      return;
    }
    if (type.indexOf("int") >= 0 || type.indexOf("float") >=0 || type.indexOf("double") >= 0) {
      value = value.replaceAll(",","");
    }
    if (type.indexOf("char") >= 0 || type.indexOf("text") >=0) {
      if (value.length() == 0) value = " ";
    }
    if (type.indexOf("date") >= 0 || type.indexOf("time") >=0) {
      if (value.trim().length() == 0) return;
    }
    params.put(field_name,value);
  }

  public void addParam(String field_name,String value)
  {
    field_name = field_name.toLowerCase();
    String type = (String)fields.get(field_name);
    addParam(field_name, value, type);
  }

  public void addParam(String field_name,int value)
  {
    addParam(field_name, "" + value);
  }

  public void addParam(String field_name,float value)
  {
    addParam(field_name, "" + value);
  }

  public void addParam(String field_name,double value)
  {
    addParam(field_name, "" + value);
  }

  public void addParam(String field_name,char value)
  {
    addParam(field_name, "" + value);
  }

  public void addParam(String field_name,java.sql.Date value)
  {
    addParam(field_name, Utils.toString(value));
  }

  public void addParam(String field_name,java.sql.Time value)
  {
    addParam(field_name, Utils.toString(value));
  }

  public void addParam(String field_name)
  {
    addParam(field_name,getRequest().getValue(field_name));
  }

  public void addParam(String field_name, Record rc)
  {
    if (rc.findColumn(field_name) >= 0) {
      addParam(field_name, rc.getString(field_name));
    }
  }

  public void addParams()
  {
    params.clear();
    Enumeration keys = fields.keys();
    while (keys.hasMoreElements()) {
      String field = (String)keys.nextElement();
      String type  = (String)fields.get(field);
      String value = getRequest().getValue(field);
      if (!field.equals("id")) {
        this.addParam(field,value,type);
      }
    }
  }

  private long findID(String key_field,String key_value)
  {
    String sql =
        "SELECT id FROM " + table_name + " " +
        "WHERE " + key_field + " = '" + key_value + "'";
    Record rc = new Record(getSQLCommand().executeQuery(sql));
    long id = rc.getLongValue("id");
    rc.close();
    return id;
  }

  public boolean hasParameters()
  {
    return !params.isEmpty();
  }

  private String getSetClause()
  {
    String result = "";
    Enumeration keys = params.keys();
    while (keys.hasMoreElements()) {
      String key   = (String)keys.nextElement();
      String value = (String)params.get(key);
      if (!value.equals("")) {
        value = value.replace('"','\'');
        result = result + "," + key + "=" + '"' + value + '"';
      }
    }
    if (result.length() > 0) return result.substring(1);
    else return null;
  }

  private long save(long id)
  {
    if (hasParameters()) {
      String sql =
          "UPDATE " + table_name + " SET " + getSetClause() + " " +
          "WHERE id = " + id;
      getSQLCommand().executeUpdate(sql);
    }
    return id;
  }

  private long newRecord()
  {
    long new_id = this.getLastID() + 1;
    this.addParam("id","" + new_id);
    String sql = "INSERT " + table_name + " SET " + getSetClause();
    if (getSQLCommand().executeUpdate(sql) > 0) return getLastID();
    else return 0;
  }

  public long update(String key_field, String key_value)
  {
    long id = (key_value != null) ? findID(key_field,key_value) : 0;
    return (id > 0) ? save(id) : newRecord();
  }

  public long update(long id)
  {
    if (id > 0) return update("id","" + id);
    else return update("id",null);
    //else return 0;
  }

  public long update(String key_field, long key_value)
  {
    return update(key_field,"" + key_value);
  }

  public boolean delete(long id)
  {
    if (id > 0) {
      String sql = "DELETE FROM " + table_name + " WHERE id = " + id;
      return getSQLCommand().executeUpdate(sql) > 0;
    } else return false;
  }

  public int setFieldValue(long id,String field_name,String field_value)
  {
    String sql =
        "UPDATE " + table_name + " " +
        "SET " + field_name + " = '" + field_value + "' " +
        "WHERE id = " + id;
    return getSQLCommand().executeUpdate(sql);
  }
}
